<?php
/**
 *  學科能力分組升級
 * @author cik
 *
 */
class Default_Model_Upgrade_Elective extends Default_Model_Upgrade_Core
{
    protected $_table = 'elective_tea';

    function upgrade (){
        if ($this->isUpgrade())
        return 'isUpgrde';
        else {
             // 先改為 MYISM
            $query = "ALTER TABLE student_score_elective_group  DROP FOREIGN KEY student_score_elective_group_ibfk_1";
            $this->_newdbPdo->exec($query);

            $query = "ALTER TABLE `student_score_elective_group`  ENGINE = MYISAM";
            $this->_newdbPdo->exec($query);

             $query = "ALTER TABLE student_score_elective_stage DROP FOREIGN KEY student_score_elective_stage_ibfk_1";
            $this->_newdbPdo->exec($query);

            $query = "ALTER TABLE `student_score_elective_stage`  ENGINE = MYISAM";
            $this->_newdbPdo->exec($query);

             $query = "ALTER TABLE student_score_elective_student  DROP FOREIGN KEY student_score_elective_student_ibfk_1";
            $this->_newdbPdo->exec($query);

            $query = "ALTER TABLE `student_score_elective_student`  ENGINE = MYISAM";
            $this->_newdbPdo->exec($query);


            $query = "SELECT a.*, b.class_id  FROM elective_tea a, score_course b WHERE
                a.course_id = b.course_id ";
            $res = $this->_sfs3Pdo->query($query)->fetchAll(PDO::FETCH_ASSOC);
            foreach($res as $row) {
                $arr = array();
                $arr['group_name'] = $this->iconv($row['group_name']);
                $arr['member'] = $row['member'];
                if ($this->iconv($row['open']) == '是')
                $arr['open'] = 1;
                else
                $arr['open'] = 0;

                $classArr = explode("_",$row['class_id']);
                $year = (int)$classArr[0];
                $semester = (int)$classArr[1];
                $grade = (int)$classArr[2];
                $classSn = (int)$classArr[3];
                $query2 = "SELECT sn FROM semester_teacher_course WHERE year='$year'
               AND semester='$semester' AND grade='$grade' AND class_sn='$classSn' AND
               subject_sn = {$row['ss_id']}  AND  teacher_sn={$row['teacher_sn']} ";
                //echo $query2;
                $res2 = $this->_newdbPdo->query($query2)->fetch(PDO::FETCH_ASSOC);
                $arr['teacher_course_sn'] = $res2['sn'];
                $arr['subject_sn'] = $row['ss_id'];
                $this->fromArray('student_score_elective_group', $arr);
                $group_sn = $this->_newdbPdo->lastInsertId();

                // 分組班傳送情形
                $query2 = "SELECT test_kind,test_sort, sum(sendmit='N') AS cc FROM
                student_score_stage WHERE subject_sn={$row['ss_id']} GROUP BY
                test_kind, test_sort";
                $res2 = $this->_newdbPdo->query($query2)->fetchAll(PDO::FETCH_ASSOC);
                foreach($res2 as $row2) {
                    $arr2 = array();
                    $arr2['group_sn'] = $group_sn;
                    $arr2['test_kind'] = $row2['test_kind'];
                    $arr2['test_sort'] = $row2['test_sort'];
                    if ($row2['cc'] > 0)
                    $arr2['sendmit'] = 'N';
                    else
                    $arr2['sendmit'] = 'Y';
                    $this->fromArray('student_score_elective_stage',$arr2);
                }

                $query3 = "SELECT * FROM elective_stu WHERE group_id={$row['group_id']}";
                $res3 = $this->_sfs3Pdo->query($query3)->fetchAll(PDO::FETCH_ASSOC);
                foreach ($res3 as $row3) {
                    $arr2 = array();
                    $arr2['group_sn']  = $group_sn;
                    $arr2['student_sn'] = $row3['student_sn'];
                    $this->fromArray('student_score_elective_student', $arr2);
                }
            }

            $query = "ALTER TABLE `student_score_elective_group`  ENGINE = INNODB";
            $this->_newdbPdo->exec($query);

             $query = "ALTER TABLE `student_score_elective_stage`  ENGINE = INNODB";
            $this->_newdbPdo->exec($query);

            $query = "ALTER TABLE `student_score_elective_student`  ENGINE = INNODB";
            $this->_newdbPdo->exec($query);

            $query = "ALTER TABLE `student_score_elective_stage`
  ADD CONSTRAINT `student_score_elective_stage_ibfk_1` FOREIGN KEY (`group_sn`) REFERENCES `student_score_elective_group` (`sn`) ON DELETE CASCADE ON UPDATE CASCADE;";
            $this->_newdbPdo->exec($query);

             $query = "ALTER TABLE `student_score_elective_student`
  ADD CONSTRAINT `student_score_elective_student_ibfk_1` FOREIGN KEY (`group_sn`) REFERENCES `student_score_elective_group` (`sn`) ON DELETE CASCADE ON UPDATE CASCADE;";
            $this->_newdbPdo->exec($query);

        }
        $log['memo'] = '分組課程設定';
        $log['upgrade_time']    = date("Y-m-d h:i:s");

        $this->finishUpgrade($log);
    }
}